﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Configuration;


namespace U8
{
    public class Inventory_gy
    {
        string strConn = ConfigurationManager.ConnectionStrings["conn1"].ConnectionString;
        string UserId = ConfigurationManager.ConnectionStrings["str"].ConnectionString;
        public Inventory_gy() 
        {
           // this.strConn = strConn;
          //  this.UserId = UserId;
        }

        public  string Save(InventoryInfo_GY bom)
        {
            DataSet ds = GetModel(bom.cInvCode);
            foreach (DataTable table in ds.Tables)
            {
                string tblName = table.TableName.ToLower();
                foreach (DataRow dr in table.Rows)
                {
                    if (tblName == "bas_part")
                    {
                        dr["invcode"] = bom.cInvCode;
                        for (int i = 1; i <=10; i++)
                        {
                            dr["free" + i] = "";
                        }
                    }
                    
                }
            }
            return Save(ds, bom);
        }


        /// <summary>
        /// 保存存货档案
        /// </summary>
        /// <param name="dsData"></param>
        /// <param name="bom"></param>
        /// <returns></returns>
        public string Save(DataSet dsData, InventoryInfo_GY bom)
        {
           
            SqlConnection Conn = new SqlConnection(strConn);
            Conn.Open();
            SqlTransaction tran = Conn.BeginTransaction();
            try
            {
                // Save(dsData, DBName, Conn, tran);
                // if (!ExistsBom(dsData.Tables["bas_Part"].Rows[0]["PartId"].ToString(), UserId))
                if (bom.PRoutingId != null)  //如果存在同版本的数据，先删除再新增
                {
                    Quary("delete sfc_proutingdinsp where PRoutinginspId in (select  PRoutinginspId from sfc_proutingdetail  where PRoutingId = " + bom.PRoutingId + ")", Conn, tran);
                    Quary("delete sfc_proutingdetail_extradefine where PRoutingDId in (select  PRoutingDId from sfc_proutingdetail  where PRoutingId = " + bom.PRoutingId + ")", Conn, tran);

                    Quary("delete sfc_prouting  where PRoutingId=" + bom.PRoutingId, Conn, tran);
                    Quary("delete sfc_proutingpart  where PRoutingId=" + bom.PRoutingId, Conn, tran);
                    Quary("delete sfc_proutingdetail  where PRoutingId=" + bom.PRoutingId, Conn, tran);
                    Quary("delete sfc_prouting_ExtraDefine where Bomid=" + bom.PRoutingId, Conn, tran);
                }
                SaveBom(dsData.Tables["bas_Part"], bom, UserId, Conn, tran);

                tran.Commit();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                return ex.Message;
            }
            return "";
        }

        /// <summary>
        /// 保存BOM信息
        /// </summary>
        /// <param name="dtPart"></param>
        /// <param name="bom"></param>
        /// <param name="Conn"></param>
        /// <param name="Tran"></param>
        public void SaveBom(DataTable dtPart, InventoryInfo_GY bom, string DBName, SqlConnection Conn, SqlTransaction Tran)
        {
            DataSet ds = GetBOMModel();
            long BomId = 0L;
            GetVoucherID(DBName.Substring(7, 3), "sfc_prouting", ref BomId);
            {
                DataRow dr = ds.Tables["sfc_proutingpart"].NewRow();
                dr["AutoId"] = Guid.NewGuid();
                dr["PRoutingId"] = BomId;
                dr["PartId"] = dtPart.Rows[0]["PartId"];
                dr["SharingPartId"] = 0;
                ds.Tables["sfc_proutingpart"].Rows.Add(dr);
            }

           
            {
                DataRow dr = ds.Tables["sfc_prouting_ExtraDefine"].NewRow();
                dr["PRoutingId"] = BomId;
                ds.Tables["sfc_prouting_ExtraDefine"].Rows.Add(dr);
            }

            {
                DataRow dr = ds.Tables["sfc_prouting"].NewRow();
                dr["PRoutingId"] = BomId;
                dr["RountingType"] = 1;
                dr["Version"] = bom.Version;
                dr["VersionDesc"] = bom.VersionDesc;
                dr["VersionEffDate"] = bom.VersionEffDate;
                dr["VersionEndDate"] = "2099-12-31";
                dr["CreateDate"] = DateTime.Now.ToString("yyyy-MM-dd");
                dr["CreateUser"] = bom.cmker;
                dr["VTid"] = 30377;
                dr["Status"] = 3;
                dr["RelsDate"] = DateTime.Now.ToString("yyyy-MM-dd");
                dr["RelsUser"] = bom.cmker;
                dr["RelsTime"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                dr["CreateTime"] = DateTime.Now.ToString("yyyy-MM-dd");
                dr["AuditStatus"] = 1;
                dr["UpdCount"] = 0;
                dr["IsWFControlled"] = 0;
                dr["iVerifyState"] = 0;
                dr["iReturnCount"] = 0;
                dr["RunCardFlag"] = 0;
                dr["PfBatchQty"] = 0;
                ds.Tables["sfc_prouting"].Rows.Add(dr);
            }
            int iRow = 1;
            //排序
            var sortedNumbers = bom.InventorySub_GY.OrderBy(n => n.row).ToList();

            foreach (var row in sortedNumbers)
            {
                long PRoutingDId = 0L;
                long PRoutinginspId = 0L;
                GetVoucherID(DBName.Substring(7, 3), "sfc_proutingdetail", ref PRoutingDId);
                GetVoucherID(DBName.Substring(7, 3), "sfc_proutingdinsp", ref PRoutinginspId);
                {
                    DataRow dr = ds.Tables["sfc_proutingdetail"].NewRow();
                    dr["PRoutingDId"] = PRoutingDId;
                    dr["PRoutinginspId"] = PRoutinginspId;
                    dr["OperationId"] = row.OperationId;
                    dr["Description"] = row.Description;
                    dr["WcId"] = row.WcId;
                    dr["PRoutingId"] = BomId;
                    dr["OpSeq"] = row.row;
                   // dr["OpSeq"] = "0000";
                    dr["EffBegDate"] = bom.VersionEffDate;
                    dr["EffEndDate"] = "2099-12-31";
                    dr["SubFlag"] =0;
                    dr["LtPercent"] = 0;
                    dr["RltOptionFlag"] =0;
                    if (bom.InventorySub_GY.Count == iRow)
                    {
                        dr["LastFlag"] = 1;
                    }
                    else
                    {
                        dr["LastFlag"] = 0;
                    }
                    dr["ReportFlag"] =1;
                    dr["BFFlag"] = 0;
                    dr["FeeFlag"] = 1;
                    dr["PlanSubFlag"] = 0;
                    dr["DeliveryDays"] = 0;
                    dr["SplitFlag"] = 0;
                    dr["Define26"] = row.Define26;

                    ds.Tables["sfc_proutingdetail"].Rows.Add(dr);
                    iRow++;
                }

                
                {
                    DataRow dr = ds.Tables["sfc_proutingdetail_extradefine"].NewRow();
                    dr["PRoutingDId"] = PRoutingDId;
                    dr["cbdefine102"] = row.cbdefine102;
                    dr["cbdefine1"] = row.cbdefine1;
                    dr["cbdefine2"] = row.cbdefine2;
                    dr["cbdefine29"] = row.cbdefine29;
                    dr["cbdefine31"] = row.cbdefine31;
                    dr["cbdefine27"] = row.cbdefine27;
                   
                    ds.Tables["sfc_proutingdetail_extradefine"].Rows.Add(dr);
                }

                {
                    DataRow dr = ds.Tables["sfc_proutingdinsp"].NewRow();
                    dr["PRoutinginspId"] = PRoutinginspId;
                    dr["QtMethod"] = 1;
                    dr["VTid"] = 0;
                    dr["ItestRule"] = 1;
                    dr["OpTransType"] = 1;
                    ds.Tables["sfc_proutingdinsp"].Rows.Add(dr);
                }
            }


            //保存数据
            foreach (DataTable dt in ds.Tables)
            {
                SqlHelper.SaveTable(dt, Conn, Tran, null, DBName);
            }
        }



        public DataSet GetModel(string cInvCode)
        {
            DataSet ds = new DataSet();
            string[] tbls = "bas_part".Split(',');
            foreach (string tbl in tbls)
            {
                try
                {
                    string sql = string.Format("Select * from {0} With(nolock) Where {1} = '{2}'", tbl, GetInventoryTableKeyField(tbl), cInvCode);
                    DataTable dt = SqlHelper.Query(sql, strConn);
                    dt.TableName = tbl;
                    ds.Tables.Add(dt);
                }
                catch (Exception ex)
                {
                }
            }
            return ds;
        }

        public DataSet GetBOMModel()
        {
            DataSet ds = new DataSet();
            string[] tbls = "sfc_prouting,sfc_proutingpart,sfc_proutingdetail,sfc_prouting_ExtraDefine,sfc_proutingdetail_extradefine,sfc_proutingdinsp".Split(',');
            foreach (string tbl in tbls)
            {
                try
                {
                    string sql = string.Format("Select * from {0} With(nolock) Where 1=2 ", tbl);
                    DataTable dt = SqlHelper.Query(sql, strConn);
                    dt.TableName = tbl;
                    ds.Tables.Add(dt);
                }
                catch (Exception ex)
                {
                }
            }
            return ds;
        }

        private string GetInventoryTableKeyField(string TableName)
        {
            switch (TableName.ToLower())
            {
                case "bas_part":
                    return "invcode";
                case "inventory_sub":
                    return "cInvSubCode";
                case "inventory_extradefine":
                    return "cinvcode";
                case "inventory":
                    return "cinvcode";
                default:
                    return "id";
            }
        }

      
        private bool ExistsInv(string cInvCode, string DBName)
        {
            try
            {
                string sql = string.Format("Select 1 from {0}..Inventory with(nolock) Where cInvCode ='{1}' ", DBName, cInvCode);
                DataTable dt = SqlHelper.Query(sql, strConn);
                return dt.Rows.Count > 0;
            }
            catch (Exception ex)
            {
                return false;
            }
        }


        private bool Quary(string sql, SqlConnection Conn, SqlTransaction Tran)
        {
            try
            {
                int a = SqlHelper.Execute(sql, Conn, Tran);
                if (a > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                return false;
            }
        }


        private string GetInvPartId(string cInvCode, string DBName)
        {
            string sql = string.Format("Select PartId from {0}..bas_Part with(nolock) Where InvCode='{1}'", DBName, cInvCode);
            DataTable dt = SqlHelper.Query(sql, strConn);
            if (dt.Rows.Count == 0)
                throw new Exception("存货编码[" + cInvCode + "]不存在");
            return dt.Rows[0][0].ToString();
        }

    

        public bool GetVoucherID(string accId, string VouchType, ref long FatherID)
        {
            string strErr = "";
            long id = 0L;
            return GetVoucherID(strConn, accId, VouchType, 1, ref id, ref FatherID, ref strErr);
        }

        public bool GetVoucherID(string strConn, string accId, string VouchType, int Rows, ref long FatherID, ref long ChildID, ref string errMsg)
        {
            Boolean result = false;
            try
            {
                using (SqlConnection conn = new SqlConnection(strConn))
                {
                    conn.Open();
                    SqlCommand GetID = conn.CreateCommand();
                    GetID.CommandType = CommandType.StoredProcedure;
                    GetID.CommandText = "sp_GetID";
                    GetID.Parameters.Add("@RemoteId", SqlDbType.VarChar, 2, "00");
                    GetID.Parameters.Add("@cAcc_Id", SqlDbType.VarChar, 3).Value = accId;
                    GetID.Parameters.Add("@cVouchType", SqlDbType.VarChar, 50).Value = VouchType;
                    GetID.Parameters.Add("@iAmount", SqlDbType.Int, 1).Value = Rows.ToString();
                    GetID.Parameters.Add("@iFatherId", SqlDbType.Int);
                    GetID.Parameters.Add("@iChildId", SqlDbType.Int);
                    GetID.Parameters["@iFatherId"].Direction = ParameterDirection.Output;
                    GetID.Parameters["@iChildId"].Direction = ParameterDirection.Output;
                    GetID.ExecuteNonQuery();
                    FatherID = Int64.Parse(GetID.Parameters["@iFatherId"].Value.ToString());
                    ChildID = Int64.Parse(GetID.Parameters["@iChildId"].Value.ToString()) - Rows;
                    GetID.Dispose();
                    result = true;
                }
            }
            catch (Exception ex)
            {
                errMsg = ex.Message;
            }
            return result;
        }
    }

 

    public class InventoryInfo_GY
    {
        public  string PRoutingId { get; set; }

        public string cmker { get; set; }
        public string cInvCode { get; set; }
        public int Version { get; set; }  //版本号
        public DateTime CreateDate { get; set; }  //版本号
        public DateTime VersionEffDate { get; set; }  //版本号
        public string VersionDesc { get; set; }  //版本号
        public List<InventorySub1_GY> InventorySub_GY { get; set; }
    }



    public class InventorySub1_GY
    {
        public string OpCode { get; set; }  //工序代号

        public string OperationId { get; set; }  //工序代号
        public string WcCode { get; set; }   //工作中心编码
        public string WcId { get; set; }   //工作中心编码
        public float Define26 { get; set; }  //工序价格

        public float cbdefine102 { get; set; }  //班产量
        public float cbdefine2 { get; set; }  //工序废品率
        public float cbdefine1 { get; set; }  //工序重量
        public float cbdefine29  { get; set; }  //辅助工时待定

        public float cbdefine31 { get; set; }//准备工时待定

        public float cbdefine27 { get; set; }//准备工时待定

        public string Description  { get; set; }//准备工时待定


        public string row { get; set; }//准备工时待定

    }

}







